# coding=utf-8
'''
Created on 2020-09-23 15:22
@author: jin
'''
import xlrd
import xlwt
import MySQLdb
import re

db = MySQLdb.connect(host="47.110.247.8", port=6612, user="test", passwd="yrkj@2020@_test", db="court_test3.0",
                     charset='utf8')


def read_excel_xls(path):
    workbook = xlrd.open_workbook(path)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    data = []
    for i in range(0, worksheet.nrows):
        d = []
        for j in range(0, worksheet.ncols):
            d.append(worksheet.cell_value(i, j))
        data.append(d)
    return data


def write_excel_xls(path, sheet_name, value):
    index = len(value)  # 获取需要写入数据的行数
    workbook = xlwt.Workbook()  # 新建一个工作簿
    sheet = workbook.add_sheet(sheet_name)  # 在工作簿中新建一个表格
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.write(i, j, value[i][j])  # 像表格中写入数据（对应的行和列）
    workbook.save(path)  # 保存工作簿
    print("xls格式表格写入数据成功！")


def read_mysql(sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        return results
    except:
        print("Error: unable to fecth data")


def get_name(id_address, r):
    if "省" in str(id_address):
        r.append("不需要查找")
        r.append(" ")
        r.append(" ")
        return
    if id_address != '':
        ss = re.split('[县市省区旗]', id_address)
        if len(ss) >= 2:
            print(ss[-2])
            results = read_mysql(sql.replace("%s", ss[-2]))
            if len(results) >= 1:
                print(results[0])
                r.extend(results[0])
            else:
                r.append("没有在数据库中找到")
                r.append(" ")
                r.append(" ")
        else:
            r.append("没有找到县市省区旗")
            r.append(" ")
            r.append(" ")
    else:
        r.append("没有找到")
        r.append(" ")
        r.append(" ")


if __name__ == '__main__':
    exec = read_excel_xls(r'C:\Users\jin\Desktop\海通要素表1600件-更新后9-8统计资料缺少名单(9-20)(5)(1).xls')
    sql = """select CONCAT(省.`name`,省.suffix,市.`name`,市.suffix,县.`name`,县.suffix),省.`name`,市.`name` from district 县 left join district 市 on 市.id=县.parent_id
left join district 省 on 省.id =市.parent_id left JOIN district 镇 on 镇.parent_id =县.id
where  县.name like '%%s%'"""
    data = [[]]
    for row in exec:
        id = row[1]
        code = row[3]
        name = row[4]
        id_address = row[10]
        address = row[12]
        print(id_address)
        r = [id, code, name, id_address, address]
        get_name(id_address, r)
        get_name(address, r)
        data.append(r)
    write_excel_xls(r'C:\Users\jin\Desktop\test.xls', 'test', data)
